const { getConnection } = require('../config/db');

class VideoGenreModel {
  // 为视频添加剧种
  static async addGenresToVideo(videoId, genreIds) {
    const connection = await getConnection();
    try {
      // 先删除原有的关联信息
      const deleteSql = 'DELETE FROM video_genres WHERE video_id = ?';
      await connection.execute(deleteSql, [videoId]);
      
      // 插入新的关联信息
      for (const genreId of genreIds) {
        const insertSql = 'INSERT INTO video_genres (video_id, genre_id) VALUES (?, ?)';
        await connection.execute(insertSql, [videoId, genreId]);
      }
      
      return { success: true };
    } finally {
      connection.release();
    }
  }
  
  // 获取视频的剧种列表
  static async getGenresByVideoId(videoId) {
    const connection = await getConnection();
    try {
      const sql = `
        SELECT g.id, g.name
        FROM genres g
        INNER JOIN video_genres vg ON g.id = vg.genre_id
        WHERE vg.video_id = ?
      `;
      const [rows] = await connection.execute(sql, [videoId]);
      return rows;
    } finally {
      connection.release();
    }
  }
  
  // 获取剧种下的视频数量
  static async getVideoCountByGenreId(genreId) {
    const connection = await getConnection();
    try {
      const sql = 'SELECT COUNT(*) as count FROM video_genres WHERE genre_id = ?';
      const [rows] = await connection.execute(sql, [genreId]);
      return rows[0].count;
    } finally {
      connection.release();
    }
  }
}

module.exports = VideoGenreModel;